var express = require('express')
var router = express.Router()
// 导入自己封装的数据库操作模块
const exec = require('../db')
//导入调试的标记
const {
  debug
} = require('../config')

/**
 * 根据数据id查询其下评论，以及每个评论对应的用户昵称，用户头像
 * GET /comments
 */
router.get('/userinfo', async (req, res) => {
  let article_id = req.query.article_id
  let sql_msg_userinfo = `SELECT comment_tb.*,user_tb.nickname,user_tb.avatar 
  FROM comment_tb LEFT JOIN user_tb ON comment_tb.user_id=user_tb.id 
  WHERE comment_tb.article_id=${article_id} && comment_tb.approved = 1`
  let sql_total = `SELECT count(*) as total
  FROM comment_tb LEFT JOIN user_tb ON comment_tb.user_id=user_tb.id
  WHERE comment_tb.article_id=${article_id} && comment_tb.approved = 1`

  try {
    let {
      total
    } = (await exec(sql_total))[0]
    let msg_userinfo = await exec(sql_msg_userinfo)
    res.send({
      code: 0,
      msg: '获取成功',
      result: {
        total,
        msg_userinfo,
      },
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '获取失败',
      result: debug ? err : '',
    })
  }
})
// 查询近七日每天新增的评论量
// GET /comments/stat
router.get('/stat', async (req, res) => {
  let sql = `
  select d.*, IFNULL(T.count,0) num from (
    SELECT CURDATE() AS date, date_format(CURDATE(),'%W') week 
		UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS date, date_format(DATE_SUB(CURDATE(), INTERVAL 1 DAY),'%W') week 
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS date, date_format(DATE_SUB(CURDATE(), INTERVAL 2 DAY),'%W') week 
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS date, date_format(DATE_SUB(CURDATE(), INTERVAL 3 DAY),'%W') week 
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS date, date_format(DATE_SUB(CURDATE(), INTERVAL 4 DAY),'%W') week 
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS date, date_format(DATE_SUB(CURDATE(), INTERVAL 5 DAY),'%W') week 
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS date, date_format(DATE_SUB(CURDATE(), INTERVAL 6 DAY),'%W') week 
  )  d
  left join(
      SELECT
      DATE_FORMAT( created_time, '%Y-%m-%d' ) days, DATE_FORMAT( created_time, '%W' ) week,
      count(*) count
      FROM

      ( SELECT * FROM comment_tb
      WHERE approved = 1 && DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date(created_time) ) as b

      GROUP BY
      days
  ) T on T.days = d.date
  
  GROUP BY d.date
  `
  // 数据响应
  try {
    res.send({
      code: 0,
      msg: '查询成功',
      result: await exec(sql),
    })
    return
  } catch (err) {
    res.send({
      code: 1,
      msg: '失败',
      result: debug ? err : '',
    })
    return
  }
})

/* 分页查询评论 */
router.get('/', async function (req, res, next) {
  // 获取请求参数
  let query = req.query
  // 创建sql语句
  let sql
  if (query.page && query.size) {
    // GET /comments/?page=1&size=5
    let sql = `select count(*) as total from comment_tb`
    let {
      total
    } = (await exec(sql))[0]

    sql = `select comment_tb.*, title article_name, nickname, avatar from comment_tb,job_tb,user_tb where article_id =  job_tb.id  ORDER BY id desc limit ${query.size * (query.page - 1)
      },${query.size}`

    // 数据响应
    try {
      res.send({
        code: 0,
        msg: '查询成功',
        result: {
          total,
          comments: await exec(sql)
        },
      })
      return
    } catch (err) {
      res.send({
        code: 1,
        msg: '失败',
        result: debug ? err : '',
      })
      return
    }
  } else if (query.article_id) {
    /* 根据数据id查询所有评论 */
    // /comments/?article_id=2
    sql = `select * from comment_tb where article_id="${query.article_id}" &&  comment_tb.approved = 0`
  } else {
    /* 查询所有评论 */
    // GET /comments/
    sql = `select * from comment_tb`
  }
  // 返回数据库操作结果
  try {
    res.send({
      code: 0,
      msg: '查询成功!',
      result: await exec(sql),
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '查询失败!',
      result: debug ? err : '',
    })
  }
})

router.post('/', async function (req, res, next) {
  /* 发布评论,添加一条评论*/
  // POST /comments/
  // 获取请求数据
  let body = req.body
  // 创建sql语句
  let sql = `insert into comment_tb (content,user_id,article_id,approved) 
  values ("${body.content}","${body.user_id}","${body.article_id}","${body.approved}")`
  // 返回数据库操作结果
  try {
    let data = await exec(sql)
    res.send({
      code: 0,
      msg: '添加成功!',
      result: data.insertId,
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '添加失败!',
      result: debug ? err : '',
    })
  }
})
router.put('/:id', async function (req, res, next) {
  /* 修改评论, 审核评论*/
  // PUT /comments/ 
  // 获取请求数据
  let body = req.body
  // 过滤需要修改的数据
  let bodyfilter = ''
  for (let key in body) {
    if (body[key] !== '') {
      bodyfilter += `${key}="${body[key]}",`
    }
  }
  bodyfilter = bodyfilter.slice(0, bodyfilter.length - 1)
  // 创建sql语句
  let sql = `update comment_tb set ${bodyfilter} where id="${req.params.id}"`
  // 返回数据库操作结果
  try {
    let data = await exec(sql)
    res.send({
      code: 0,
      msg: '修改成功!',
      result: '',
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '修改失败!',
      result: debug ? err : '',
    })
  }
})

router.delete('/', async function (req, res, next) {
  /* 根据id删除评论 */
  // DELETE /comments/
  // 获取请求参数
  let query = req.query
  // 创建sql语句
  let sql = `delete from comment_tb where id=${query.id}`
  // 返回数据库操作结果
  try {
    let data = await exec(sql)
    res.send({
      code: 0,
      msg: '删除成功!',
      result: '',
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '删除失败!',
      result: debug ? err : '',
    })
  }
})

// 导出路由对象
module.exports = router